package SqlJavaBean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class User {
	private Connection con;
	private Statement run;
	private String STATUS;
	private ResultSet rs;
	private String res;
	private DBBean db;
	
	public User(){
		init();
	}
	private void init(){
		db = new DBBean();
		con = db.getConnection();
		try{
			run = con.createStatement();
		}
		catch(Exception e){
		}
	}
	
	public Connection userLogin(String user,String passwd){
		try{
			rs = run.executeQuery("select * from User where user='"+user+"';");
			if(rs.next()) return db.getConnection(user,passwd);
		}
		catch(Exception e){
			return null;
		}
		return null;
	}
	public Connection adminLogin(String user,String passwd){
		try{
			rs = run.executeQuery("select * from Admin where user='"+user+"';");
			if(rs.next()) return db.getConnection(user,passwd);
		}
		catch(Exception e){
			return null;
		}
		return null;
	}
	public String getRes(){
		return this.res;
	}
	public boolean register(String user,String passwd,String sid,String email,String name){
		if(!checkUser(user,sid)) return false;
		if(!createUser(user,passwd)) return false;
		if(!insertUser(user,passwd,email,name,sid)) return false;
		return true;
	}
	private boolean checkUser(String name,String sid){
		try{
			Statement ss = con.createStatement();
			ss.execute("use mysql");
			PreparedStatement step2 = con.prepareStatement("select User,Host from user where User=?");
			step2.setString(1,name);
			rs = step2.executeQuery();
			if(rs.next()){
				res = "此用户名被抢占啦！请换个名字再试试呀！";
				return false; //用户名已存在
			}
			else			//用户不存在，可以继续往下注册
			{
				Statement sta = con.createStatement();
				sta.execute("use websql");
				ResultSet r =  sta.executeQuery("select * from User where User='"+name+"' or sid='"+sid+"'");
				if(r.next()){
					res =res+ "此用户已存在！可以尝试找回密码！";
					return false;
				}
				else {
					res =res+ "okokokok";
					return true;
				}
			}
		}
		catch(Exception e){
			res =res+ "查询用户信息失败！"+e.getMessage();
			return false;
		}
		
	}
	private boolean createUser(String name,String passwd){
		try{
			String test = "grant all privileges on "+name+".* to "+name+"@'%' identified by '"+passwd+"'";
			Statement prepare = con.createStatement();
			prepare.execute(test);
			prepare.execute("create database "+name);
			return true;
		}
		catch(Exception e){
			try{
				Statement s = con.createStatement();
				//drop user 'jack'@'localhost';
				s.execute("use mysql;");
				s.execute("drop user '"+name+"'@'%'");
				s.execute("drop database "+name);
			}
			catch(Exception x){
				res = res+x.getMessage();
			}
			res = res+ " 注册用户失败！"+e.getMessage();
			return false;
		}
	}
	
	public boolean insertUser(String User,String passwd,String email,String name,String sid){
		try{
			res = "1";
			Statement s = con.createStatement();
			res = "2";
			s.execute("use websql;");
			res = "3";
			PreparedStatement insert= con.prepareStatement("insert into User values(?,?,?,?,?)");
			res = "4";
			insert.setString(1, User);
			insert.setString(2, passwd);
			insert.setString(3, sid);
			insert.setString(4, email);
			insert.setString(5, name);
			res = "5";
			insert.execute();
			res = "6";
		}
		catch(Exception e){
			res = res+ " :插入用户信息失败！"+e.getMessage();
			return false;
		}
		return true;
	}
}
